<?php defined('DML') or die("Access locked");

class MySQL{
   private $resource = NULL;
   private $tpl = "";
   
   protected $strSQL = "";
   public $result = NULL;
   public $error = "";
   public $total_queries = 0;
   public $totalrecords = NULL;


   public function __construct($host, $user, $passw, $db, $tpl = ""){
      $this->tpl = $tpl;
      $this->resource = mysql_connect($host, $user, $passw) or die (mysql_error());
      $version = substr($this->findVersionNumber(), 4);
      if($version > 6){
         mysql_set_charset("utf8");
      }else{
         mysql_query("SET NAME 'utf8'");
         mysql_query("SET CHARSET SET 'utf8'");
      }
      mysql_select_db($db, $this->resource) or die (mysql_error());
   }
   
   public function __destruct() {
      $this->close();
   }
   
   private function close(){
      if(isset($this->result)){
         mysql_free_result($this->result);
         unset($this->result);
      }
      mysql_close($this->resource);
      unset ($this->resource);
   }

   private function findVersionNumber(){
      return mysql_get_server_info($this->resource);
   }

   protected function Sanitize($value){
      if (get_magic_quotes_gpc()){
         $value = stripslashes($value);
      } 
      $value = mysql_real_escape_string($value);
      return $value;
   }
   
   private function getMonth($month){
      switch($month){
         case 01:   $month = "Январь";      break;
         case 02:   $month = "Февраль";     break;
         case 03:   $month = "Март";        break;
         case 04:   $month = "Апрель";      break;
         case 05:   $month = "Май";         break;
         case 06:   $month = "Июнь";        break;
         case 07:   $month = "Июль";        break;
         case 08:   $month = "Август";      break;
         case 09:   $month = "Сентябрь";    break;
         case 10:   $month = "Октябрь";     break;
         case 11:   $month = "Ноябрь";      break;
         case 12:   $month = "Декабрь";     break;
      }
      return $month;
   }

   public function Query(){
      $this->result = mysql_query($this->strSQL);
      if($this->result){
         $this->total_queries++;
         return TRUE;
      }else{
         $this->error = mysql_errno().'<br />'.mysql_error();
         return FALSE;
      }
   }
   
   public function getRow(){
      return mysql_fetch_array($this->result);
   }

   public function ParseTemplate($key){
      $html = "";
      while($row = $this->getRow()){
         if(isset($row['records_month'])){
            $row['records'] = $row['records_count'];
            $row['year'] = $row['records_year'];
            $row['month'] = $row['records_month'];
            $row['month_char'] = $this->getMonth($row['records_month']);
         }
         $html = $html.ParseTamplatePath($row, $this->tpl[$key]);
      }
      return $html;
   }

   public function Select($start, $perpage){
      $start = ($start - 1) * $perpage;
      $author = $_SESSION['userid'];
      $this->strSQL = "SELECT SQL_CALC_FOUND_ROWS * 
                       FROM `dml_notes`
                       WHERE `author` = $author
                       ORDER BY `date` DESC
                       LIMIT $start, $perpage";
      $this->Query();
      $this->Rows();
   }

   public function SelectOnDate($start, $perpage){
      $start = ($start - 1) * $perpage;
      $year = $this->Sanitize($_GET['year']);
      $month = $this->Sanitize($_GET['month']);
      $author = $_SESSION['userid'];
      $this->strSQL = "SELECT SQL_CALC_FOUND_ROWS * 
                       FROM `dml_notes` 
                       WHERE YEAR(`date`) = '$year' 
                       AND MONTH(`date`) = '$month' 
                       AND `author` = '$author'
                       ORDER BY `date` DESC 
                       LIMIT $start, $perpage";
      $this->Query();
      $this->Rows();
   }

   public function ArchiveNotes(){
      $author = $_SESSION['userid'];
      $this->strSQL = "SELECT COUNT(*) AS `records_count`, 
                       MONTH(`date`) AS `records_month`, 
                       YEAR(`date`) AS `records_year` 
                       FROM `dml_notes` 
                       WHERE `author` = '$author'
                       GROUP BY CONCAT(MONTH(`date`),'-',YEAR(`date`))
                       ORDER BY `date` DESC";
      $this->Query();
      
   }
   
   public function AddNote($author, $note){
      $author = $this->Sanitize($author);
      $date = @date("Y-m-d H:i:s");
      $note = $this->Sanitize($note);
      
      $this->strSQL = "INSERT INTO `dml_notes`
                      (`author`, `date`, `note`)
                      VALUES ('$author', '$date', '$note')";
      return $this->Query();
   }
   
   public function DeleteNote($id){
      $author = $_SESSION['userid'];
      $id = $this->Sanitize($id);
      $this->strSQL = "DELETE FROM `dml_notes`
                       WHERE `id`=$id
                       AND `author` = '$author'";
      
      return $this->Query();
   }
   
   public function AddUserSQL($login, $passw, $email, $role){
   	$date = @date("Y-m-d H:i:s");
   	$login = $this->Sanitize($login);
   	$passw = $this->Sanitize($passw);
   	$email = $this->Sanitize($email);
   	$role = $this->Sanitize($role);
   	$this->strSQL = "INSERT INTO `dml_users`
                      (`login`, `email`, `passw`, `role`, `registration`)
                      VALUES ('$login', '$email', '$passw', '$role', '$date')";
   	$bln = TRUE;
   	if($this->Query() == FALSE){
   		$bln = FALSE;
   	}
   	return $bln;   	
   }
   
   public function SelectUser(){
      $str = "";
      $this->strSQL = "SELECT `id`, `login`, `role`, `registration`
                       FROM `dml_users`
                       ORDER BY `registration` DESC";
      $this->Query();
      while ($row = mysql_fetch_array($this->result)) {
         foreach ($row as $key => $value) {
            $$key = $value;
         }
         $str = $str."<tr><td>$id</td><td>$login</td><td>$role</td><td>$registration</td></tr>";
      }
      return $str;
   }
   
   public function CheckUser($login, $passw){
      $login = $this->Sanitize($login);
      $passw = $this->Sanitize($passw);
      $this->strSQL = "SELECT SQL_CALC_FOUND_ROWS `id`, `login`, `passw`, `role`
                       FROM `dml_users`
                       WHERE `login`='$login' AND `passw`='$passw'";
      $this->Query();
   }
   
   public function UsersRows(){
      mysql_num_rows($this->result);
   }

   public function Rows(){
      $this->totalrecords = mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0);
      $this->total_queries++;
   }
}
?>